/*
 * This class will handle all database traffic, so we can have a persistent
 * connection across all of our windows.
 */

/**
 *
 * @author Dustin
 */
// You need to import the java.sql package to use JDBC
import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;
import java.util.ArrayList;
import javax.swing.*; //REMOVE ME--DEBUG TEST ONLY


public class dbAccess {
    private final String dbUser = "ops$gtg624x";
    private final String dbPassword = "jackcrap";
    private final String alterDateFormat = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD'";
    private final String currDateSQL = "SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY') FROM dual"; //Returns 04-25-2008
    private Connection conn;
    private String userEmail;
    private String userTable;
    private int userID = -1;
    ArrayList<java.lang.Integer> searchJobID;
    ArrayList<java.lang.Integer> statusJobID;
    ArrayList<java.lang.Integer> searchApplicantID;
    private final String[] statusStrings = {"In test process", "In interview process", 
                            "In decision process", "Accepted", "Declined"};
    
    dbAccess() {
        searchJobID = new ArrayList<java.lang.Integer>();
        statusJobID = new ArrayList<java.lang.Integer>();
        searchApplicantID = new ArrayList<java.lang.Integer>();
    }
    
    public void connect() throws SQLException {
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:" + dbUser + "/" + dbPassword + "@" + "acme.gatech.edu:1525:public");
        conn = ods.getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute(alterDateFormat);
    }
    
    public boolean createNewApplicant(String name, int birthYear, String email, 
                String password, String phone, int yearsExp, String degree, 
                String citizenship, String description) throws SQLException {
        int retVal = -1;
        //INSERT INTO APPLICANT VALUES (&Applicant_ID, &Name, &Birth_year, &Email, &Password,
        //&Phone, &Years_of_Experience, &Highest_degree, &Citizenship, &Short_description)
        String query = "INSERT INTO ops$gtg624x.APPLICANT(APPLICANT_ID, A_NAME, EMAIL, " +
                "PASSWORD, PHONE, YEARS_OF_EXPERIENCE, HIGHEST_DEGREE, CITIZENSHIP, " +
                "SHORT_DESCRIPTION, BIRTH_YEAR) VALUES (" +
            "AppIDSeq.nextval," + //applicant ID
            "'" + name + "'," + //Name
            "'" + email + "'," + //Email
            "'" + password + "'," + //Password
            "'" + phone + "'," + //Phone
            "'" + yearsExp + "'," + //Years Experience
            "'" + degree + "'," + //Degree
            "'" + citizenship + "'," + //Citizenship
            "'" + description + "'," +
            "to_date('" + birthYear + "', 'YYYY'))"; //Birth year;
        PreparedStatement pstmt = conn.prepareStatement(query);
        retVal = pstmt.executeUpdate();
        if (retVal < 0) {
            return false;
        } else {
            userEmail = email;
            userTable = "APPLICANT";
            findUserID();
            return true;
        }
    }
    
    public int createNewRecruiter(String[] recruiterInfo) throws SQLException {
        int retVal = -1;
        String query = "INSERT INTO ops$gtg624x.RECRUITER(RECRUITER_ID, " +
            "COMPANY_NAME, WEBSITE, CONTACT_PERSON, EMAIL, PASSWORD, PHONE, FAX, " +
            "SHORT_DESCRIPTION) VALUES (" +
            "RecIDSeq.nextval," + //recruiter ID
            "'" + recruiterInfo[0] + "'," + //company Name
            "'" + recruiterInfo[6] + "'," + //web site
            "'" + recruiterInfo[1] + "'," + //contact name
            "'" + recruiterInfo[2] + "'," + //Email
            "'" + recruiterInfo[3] + "'," + //Password
            "'" + recruiterInfo[4] + "'," + //Phone
            "'" + recruiterInfo[5] + "'," + //fax
            "'" + recruiterInfo[7] + "')"; //Short description
        PreparedStatement pstmt = conn.prepareStatement(query);
        retVal = pstmt.executeUpdate();
        if (retVal < 0) {
            return retVal;
        } else {
            userEmail = recruiterInfo[2]; //FIXED: reference ID #s instead of emails
            userTable = "RECRUITER";
            findUserID(); //FIXED
            return retVal;
        }
    }
    
    public int createNewJob(String[] jobData) throws SQLException {
        /*jobData[0] = jobTitleTxt.getText(); //NEED TO VERIFY EXISTS
        jobData[1] = numPositionsSpn.getValue().toString(); //Will always be >=0
        jobData[2] = industrySel.getSelectedItem().toString(); //Any or selected
        jobData[3] = posTypeBtnGrp.getSelection().toString(); //Defaulted to Full Time
        jobData[4] = minSalaryTxt.getText(); //Assume if they leave as 0, they mean it
        jobData[5] = testSel.getSelectedItem().toString(); //No Test or selected
        jobData[6] = minTestScoreSpn.getValue().toString(); //always >=0
        jobData[7] = emailTxt.getText(); //NEED TO VERIFY EXISTS
        jobData[8] = "UNUSED"
        jobData[9] = "UNUSED"
        jobData[10] = jobDescriptionTxt.getText(); //Will be null if DNE*/
        int testID = 0, retVal = -1;
        String query = null;
        //Statement stmt = conn.createStatement();
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean test0Exists = false;
        String companyName = "EMPLOYER";
        
        query = "SELECT * FROM RECRUITER WHERE RECRUITER_ID = " + userID;
        pstmt = conn.prepareStatement(query);
        rset = pstmt.executeQuery();
        if (rset.next()) {
            companyName = rset.getString("COMPANY_NAME");
        }
        
        
        query = "SELECT * FROM TEST WHERE TEST_ID = 0";
        pstmt = conn.prepareStatement(query);
        rset = pstmt.executeQuery();
        if (rset.next()) {
            test0Exists = true;
        }
        
        if(jobData[5].equalsIgnoreCase("No Test")) {
            //No test, don't need to add test
            testID = 0;
            if(!test0Exists) {
                query = "INSERT INTO OPS$GTG624X.TEST VALUES (" +
                        "0, 'No Test', 0)";
                pstmt = conn.prepareStatement(query);
                retVal = pstmt.executeUpdate(); //Put our test in
            }
        } else {
            query = "INSERT INTO OPS$GTG624X.TEST VALUES (" +
                    "testIDseq.nextval, '" +
                    jobData[5] + "', " + 
                    jobData[6] + ")";
            pstmt = conn.prepareStatement(query);
            retVal = pstmt.executeUpdate(); //Put our test in
            query = "SELECT * FROM TEST WHERE TEST_NAME = '" + jobData[5] + "'" + 
                    "AND MIN_SCORE = '" + jobData[6] + "'";
            pstmt = conn.prepareStatement(query); //Get the testID value of the test we just added
            rset = pstmt.executeQuery();
            if (rset.next()) {
                testID = rset.getInt("TEST_ID");
            }
        }
        
        query = "INSERT INTO OPS$GTG624X.JOB(JOB_ID, EMPLOYER, JOB_DESCRIPTION, " +
                "INDUSTRY, JOB_TITLE, MIN_SALARY, RECRUITER_ID, CONTACT_EMAIL, " +
                "DATE_JOB_POSTED, DATE_JOB_FILLED, POSITION_TYPE, NUM_OF_AVAIL_POSITION, " +
                "NUM_OF_TOTAL_POSITION, NUM_OF_FILLED_POSITION, NUM_OF_UNFILLED_POSITION, " +
                "NUM_OF_NEW_APP, TEST_ID, JOB_LOCATION) VALUES (" +
                "JobIDSeq.nextval, " + //Job ID #
                "'" + companyName + "', " + //Copying employer into Job table b/c it makes life easy
                "'" + jobData[10] + "'," +
                "'" + jobData[2] + "'," + 
                "'" + jobData[0] + "'," +
                "'" + jobData[4].replaceAll(",", "") + "'," + 
                userID + "," +
                "'" + jobData[7] + "'," +
                "(select sysdate from dual)," +
                "NULL," + 
                "'" + jobData[3] + "'," +
                "'" + jobData[1] + "'," +
                "'" + jobData[1] + "'," + 
                0 + "," + 
                "'" + jobData[1] + "'," +
                0 + "," + 
                testID + "," + 
                "'" + jobData[11] + "')"; 
        System.out.println("testID: " + testID + "\n:recID: " + userID);
        System.out.println(query);
        pstmt = conn.prepareStatement(query);
        retVal = pstmt.executeUpdate();
        return retVal;
    }
    
    public String[] getApplicantProfile() throws SQLException {
        String[] toRet = new String[6];
        /*to_char(BIRTH_YEAR, 'YYYY')*/
        String query = "SELECT phone, highest_degree, to_char(birth_year, 'YYYY'), years_of_experience, " +
                "citizenship, short_description FROM ops$gtg624x.APPLICANT " +
                "where EMAIL = '" + userEmail + "'";
        /*SELECT Phone, Highest_degree, birth_year, Years_of_experience, Citizenship, 
Short_description FROM Applicant WHERE email = '&userEmail'*/
	PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rset = pstmt.executeQuery();
        rset.next();
        
        for(int i = 0; i < 6; i++) {
            toRet[i] = rset.getString(i+1);
        }
        return toRet;
    }
    
    public String[] getApplicantProfileForRecruiter(int index) throws SQLException {
        String[] toRet = new String[8];
        int id = searchApplicantID.get(index);
        /*to_char(BIRTH_YEAR, 'YYYY')*/
        String query = "SELECT a_name, email, phone, highest_degree, years_of_experience, " +
                "citizenship, short_description, to_char(birth_year, 'YYYY') FROM ops$gtg624x.APPLICANT " +
                "where APPLICANT_ID = " + id;
	PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rset = pstmt.executeQuery();
        rset.next();
        
        for(int i = 0; i < 8; i++) {
            toRet[i] = rset.getString(i+1);
        }
        return toRet;
    }
    
    public String[] getApplicantProfileWithID(int applicantID) throws SQLException {
        String[] toRet = new String[8];
        /*to_char(BIRTH_YEAR, 'YYYY')*/
        String query = "SELECT a_name, email, phone, highest_degree, years_of_experience, " +
                "citizenship, short_description, to_char(birth_year, 'YYYY') FROM ops$gtg624x.APPLICANT " +
                "where APPLICANT_ID = " + applicantID;
	PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rset = pstmt.executeQuery();
        rset.next();
        
        for(int i = 0; i < 8; i++) {
            toRet[i] = rset.getString(i+1);
        }
        return toRet;
    }
    
    public boolean updateApplicantProfile(String[] profile) throws SQLException {
        int retVal = -1;
        int yearsExp = Integer.parseInt(profile[3]);
        String query = "UPDATE ops$gtg624x.APPLICANT SET " +
            "phone = '" + profile[0] + "', " + //Phone
            "highest_degree = '" + profile[1] + "', " + //Degree
            "birth_year = to_date('" + profile[2] + "', 'YYYY'), " + //Birth year
            "years_of_experience = '" + yearsExp + "', " + //Years Experience
            "citizenship = '" + profile[4] + "', " + //Citizenship
            "short_description = '" + profile[5] + "' " + //Short description
            "WHERE email = '" + userEmail + "'";
        /*UPDATE Applicant SET Phone = &phone, Highest_degree = &highest_deg, birth_year 
Years_of_experience= &yrs_of_exp, Citizenship = &citizenship,
Short_description = &short_descrip WHERE email = '&userEmail'*/
        PreparedStatement pstmt = conn.prepareStatement(query);
        retVal = pstmt.executeUpdate();
        if (retVal < 0) {
            return false;
        } else {
            return true;
        }
    }
    
    public String[] getRecruiterProfile() throws SQLException {
        String[] toRet = new String[4];
        String query = "SELECT PHONE, FAX, WEBSITE, SHORT_DESCRIPTION " +
                "FROM ops$gtg624x.RECRUITER " +
                "WHERE EMAIL = '" + userEmail + "'";
        PreparedStatement pstmt = conn.prepareStatement(query);
        /*SELECT Phone, Highest_degree, birth_year, Years_of_experience, Citizenship, 
Short_description FROM Applicant WHERE email = '&userEmail'*/
	
        ResultSet rset = pstmt.executeQuery();
        rset.next();
        
        for(int i = 0; i <4; i++) {
            toRet[i] = rset.getString(i+1);
        }
        return toRet;
    }
    
    public boolean updateRecruiterProfile(String[] profile) throws SQLException {
        String query = "UPDATE ops$gtg624x.RECRUITER SET " +
            "PHONE = '" + profile[0] + "', " + //Phone
            "FAX = '" + profile[1] + "', " + //Degree
            "WEBSITE = '01-JAN-" + profile[2] + "', " + //Birth year
            "SHORT_DESCRIPTION = '" + profile[3] + "' " + //Short description
            "WHERE email = '" + userEmail + "'";
        PreparedStatement pstmt = conn.prepareStatement(query);
        int retVal = -1;
        retVal = pstmt.executeUpdate();
        if (retVal < 0) {
            return false;
        } else {
            return true;
        }
    }
    
    public boolean checkPassword(String email, String password, String table) throws SQLException {
        String query = "select PASSWORD, " + table + "_ID from ops$gtg624x." + table +
                " where EMAIL = '" + email + "'";
        PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rset = pstmt.executeQuery();
        //select PASSWORD from OPS$GTG624X.[table] where EMAIL = '[user input]'
        if(rset.next()) {
            if(password.equals(rset.getString(1))) {
                userEmail = email; //So we know who we are working for
                userTable = table;
                findUserID();
                return true;
            }
        }
        return false;
    }
    
/*    private void testFunction() throws SQLException {
        Statement stmt = conn.createStatement();
        //stmt.execute(alterDateFormat);
        ResultSet rset = stmt.executeQuery(currDateSQL);
        if(rset.next()) {
            System.out.println("SYSDATE: '" + rset.getString(1) + "'");
        }
    }*/
    
    public String getEmail() {
        return userEmail;
    }
    
    public int findUserID() {
        int retVal = -1;
        String query = "select * from ops$gtg624x." + userTable +
                " where EMAIL = '" + userEmail + "'";
        if (conn == null) {
            throw new IllegalStateException("Connection already closed.");
        }
        try {
        PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rset = pstmt.executeQuery ();
        if(rset.next()) {
            retVal = rset.getInt(userTable + "_ID"); //So we know who we are working for
            //System.out.println("retVal: " + retVal); //FIX
            }
        } catch (SQLException se) {
            System.out.println("Failed to get user ID!");
        }
        userID = retVal;
        return retVal;
    }
    
    /**
     * Found at: http://www.oreillynet.com/pub/a/oreilly/java/news/javaex_1000.html
     * This method takes a SQL query, passes it to the database, obtains the
     * results as a ResultSet, and returns a ResultSetTableModel object that
     * holds the results in a form that the Swing JTable component can use.
     **/
    public ResultSetTableModel getResultSetTableModel(String query)
        throws SQLException
    {
	// If we've called close(), then we can't call this method
	if (conn == null)
	    throw new IllegalStateException("Connection already closed.");

	// Create a Statement object that will be used to excecute the query.
	// The arguments specify that the returned ResultSet will be 
	// scrollable, read-only, and insensitive to changes in the db.
	Statement statement =
	    conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
				       ResultSet.CONCUR_READ_ONLY);
	// Run the query, creating a ResultSet
	ResultSet r = statement.executeQuery(query);
	// Create and return a TableModel for the ResultSet
	return new ResultSetTableModel(r);
    }
    
    /*Query the database and return a Table of the search results*/
    public javax.swing.table.TableModel searchJobs(String query) 
                        throws SQLException { 
        if (conn == null)
            throw new IllegalStateException("Connection already closed.");
        searchJobID.clear();
        PreparedStatement pstmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                    ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = pstmt.executeQuery();
        javax.swing.table.DefaultTableModel model = new javax.swing.table.DefaultTableModel() {
            public boolean isCellEditable(int rowIndex, int mColIndex) {
                return false;
            }
        };
        
        //Job Title, Employer, Location, Pos. Type, Industry, Min. Salary
        model.addColumn(new String("Job Title"));
        model.addColumn(new String("Employer"));
        model.addColumn(new String("Location"));
        model.addColumn(new String("Position Type"));
        model.addColumn(new String("Industry"));
        model.addColumn(new String("Min Salary"));
        while (rs.next()) {
            /*For each row, get the fields - must use Object for JTable*/
            Object[] data = new Object[6];
            data[0] = rs.getObject("JOB_TITLE");
            data[1] = rs.getObject("EMPLOYER");
            data[2] = rs.getObject("JOB_LOCATION");
            data[3] = rs.getObject("POSITION_TYPE");
            data[4] = rs.getObject("INDUSTRY");
            data[5] = rs.getObject("MIN_SALARY");
            model.addRow(data);
            //Add the JOB_ID to our array list, so we can reference the job if the user wants more detail
            //This will work because the index will be the same as the row clicked on the table
            searchJobID.add((int)rs.getInt("JOB_ID"));
        }
        if(model.getRowCount() > 0)
            return model;
        else
            return null;
    }
    
    public String[] getJobDetail(int index) throws SQLException {
        String[] toRet = new String[11];
        String query = new String();
        int recruiterID, testID;
        if (conn == null)
            throw new IllegalStateException("Connection already closed.");
        if (searchJobID.isEmpty())
            return null;
        query = "SELECT * FROM ops$gtg624x.JOB WHERE JOB_ID = '" + 
                    searchJobID.get(index).toString() + "'";
        PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rset = pstmt.executeQuery();
        if(rset.next()) {
            //Got our row back, so populate our String[]
            toRet[0] = rset.getString("JOB_TITLE");
            toRet[1] = rset.getString("NUM_OF_TOTAL_POSITION");
            toRet[2] = rset.getString("INDUSTRY");
            toRet[3] = rset.getString("POSITION_TYPE");
            toRet[4] = rset.getString("MIN_SALARY");
            toRet[7] = rset.getString("CONTACT_EMAIL");
            toRet[10] = rset.getString("JOB_DESCRIPTION");
            testID = rset.getInt("TEST_ID");
            recruiterID = rset.getInt("RECRUITER_ID");
        } else {
            //Got nothing--ERROR: Something went wrong...
            return null;
        }
        //Got all we need from JOB table, move on to Test and Recrutier
        query = "SELECT * FROM ops$gtg624x.RECRUITER WHERE RECRUITER_ID = '" + 
                    recruiterID + "'";
        pstmt = conn.prepareStatement(query);
        rset = pstmt.executeQuery();
        if(rset.next()) {
            toRet[8] = rset.getString("PHONE");
            toRet[9] = rset.getString("FAX");
        } else {
            toRet[8] = toRet[9] = "QUERY ERROR!";
        }
        //testID == 0 when there is no test required
        if (testID == 0) {
            toRet[5] = "No test required";
            toRet[6] = "Not available";
        } else {
            query = "SELECT * FROM ops$gtg624x.TEST WHERE TEST_ID = '" +
                            testID + "'";
            pstmt = conn.prepareStatement(query);
            rset = pstmt.executeQuery();
            if(rset.next()) {
                toRet[5] = rset.getString("TEST_NAME");
                toRet[6] = rset.getString("MIN_SCORE");
            } else {
                toRet[5] = toRet[6] = "QUERY ERROR!";
            }
        }
        return toRet;
    }
    
    public String[] getCompanyDetail(int index) throws SQLException {
        String[] toRet = new String[6];
        String query = new String();
        String recruiterID = new String();
        if (conn == null)
            throw new IllegalStateException("Connection already closed.");
        if (searchJobID.isEmpty())
            return null;
        query = "SELECT RECRUITER_ID FROM ops$gtg624x.JOB WHERE JOB_ID = '" + 
                    searchJobID.get(index).toString() + "'";
        PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rset = pstmt.executeQuery();
        if(rset.next()) {
            recruiterID = rset.getString("RECRUITER_ID");
            query = "SELECT * FROM ops$gtg624x.RECRUITER WHERE RECRUITER_ID = '" +
                    recruiterID + "'";
        } else {
            return null;
        }
        pstmt = conn.prepareStatement(query);
        rset = pstmt.executeQuery();
        if(rset.next()) {
            toRet[0] = rset.getString("CONTACT_PERSON");
            toRet[1] = rset.getString("EMAIL");
            toRet[2] = rset.getString("PHONE");
            toRet[3] = rset.getString("FAX");
            toRet[4] = rset.getString("WEBSITE");
            toRet[5] = rset.getString("SHORT_DESCRIPTION");
        } else {
            return null;
        }
        return toRet;
    }
    
    public javax.swing.table.TableModel getApplicantStatusTable(String query)
                throws SQLException {
        int appID = 0;
        String fullQuery = null;
        if (conn == null)
            throw new IllegalStateException("Connection already closed.");
        statusJobID.clear();
        fullQuery = query.concat(userID + "'");
        PreparedStatement pstmt = conn.prepareStatement(fullQuery, ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                    ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = pstmt.executeQuery();
        javax.swing.table.DefaultTableModel model = new javax.swing.table.DefaultTableModel() {
            public boolean isCellEditable(int rowIndex, int mColIndex) {
                return false;
            }
        };
        
        model.addColumn(new String("Job Title"));
        model.addColumn(new String("Employer"));
        model.addColumn(new String("Date Applied"));
        model.addColumn(new String("Status"));
        while (rs.next()) {
            /*For each row, get the fields - must use Object for JTable*/
            Object[] data = new Object[4];
            data[0] = rs.getObject("JOB_TITLE");
            data[1] = rs.getObject("COMPANY_NAME");
            //data[2] = rs.getObject("DATE_APPLIED");
            data[2] = rs.getObject(5); //Updated for better date formatting
            data[3] = rs.getObject("STATUS");
            model.addRow(data);
            //Add the JOB_ID to our array list, so we can reference the job if the user wants more detail
            //This will work because the index will be the same as the row clicked on the table
            searchJobID.add((int)rs.getInt("JOB_ID"));
        }
        if(model.getRowCount() > 0)
            return model;
        else
            return null;
    }
    
    public javax.swing.table.TableModel getRecruiterStatusTable() 
                                                throws SQLException { 
        if (conn == null)
            throw new IllegalStateException("Connection already closed.");
        if (!statusJobID.isEmpty()) {
            statusJobID.clear();
        }
        javax.swing.table.DefaultTableModel model = new javax.swing.table.DefaultTableModel() {
            public boolean isCellEditable(int rowIndex, int colIndex) {
                if (colIndex == 0) {
                    return true;
                } else {
                    return false;
                }
            }
            //  Returning the Class of each column will allow different
            //  renderers to be used based on Class
            public Class getColumnClass(int column)
            {
                if(column == 0)
                    return Boolean.class;
                else
                    return String.class;
            }
        };
        //Selected, Job ID, Job Title, #*wait for test, #*interview, #*decision, # filled pos, # req. pos., date posted
        model.addColumn(new String("Select"));
        model.addColumn(new String("Job ID"));
        model.addColumn(new String("Job Title"));
        model.addColumn(new String("*test"));
        model.addColumn(new String("*interview"));
        model.addColumn(new String("*decisions"));
        model.addColumn(new String("filled positions"));
        model.addColumn(new String("requested postions"));
        model.addColumn(new String("Date Posted"));
        //Column headers set for table--next is the hard part--gotta get this 5h1t from everywhere
        String query = "SELECT JOB_ID, JOB_TITLE, DATE_JOB_POSTED, " + 
                "NUM_OF_FILLED_POSITION, NUM_OF_TOTAL_POSITION " +
           "FROM JOB " + 
           "WHERE RECRUITER_ID = " + userID;
        PreparedStatement pstmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                    ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = pstmt.executeQuery();
        //int i = 0; //DEBUG
        while (rs.next()) {
            //System.out.println(i + ": rs.next() executed\n"); //DEBUG
            //i++; //DEBUG
            int tempJobID = rs.getInt("JOB_ID");
            //Add the JOB_ID to our array list, so we can reference the job if the user wants more detail
            //This will work because the index will be the same as the row clicked on the table
            statusJobID.add(tempJobID);
            query = "SELECT SUM(DECODE(STATUS,'In interview process',1,0)) " +
                "TOTAL_INTERVIEW_PROCESS, " + 
                "SUM(DECODE(STATUS,'In decision process',1,0)) TOTAL_DECISION_PROCESS, " +
                "SUM(DECODE(STATUS,'In test process',1,0)) TOTAL_TEST_PROCESS " +
                "FROM APPLICATION, APPLICATION_STATUS " +
                "WHERE APPLICATION.APPLICATION_ID = APPLICATION_STATUS.APPLICATION_ID AND " +
                    "APPLICATION.JOB_ID = " + tempJobID;
            Statement stmt = conn.createStatement();
            ResultSet rs2 = stmt.executeQuery(query);
            Object[] data = new Object[9];
            data[0] = new Boolean(false);
            data[1] = tempJobID;
            data[2] = rs.getString("JOB_TITLE");
            if (rs2.next()) {
                //System.out.println(i + ": rs2.next() executed\n"); //DEBUG
                data[3] = rs2.getString("TOTAL_TEST_PROCESS");
                data[4] = rs2.getString("TOTAL_INTERVIEW_PROCESS");
                data[5] = rs2.getString("TOTAL_DECISION_PROCESS");
            }
            data[6] = rs.getString("NUM_OF_FILLED_POSITION");
            data[7] = rs.getString("NUM_OF_TOTAL_POSITION");
            data[8] = rs.getString("DATE_JOB_POSTED");
            model.addRow(data);
        }
        return model;
    }
    
    public javax.swing.table.TableModel jobApplicationsStatus(int jobID) 
            throws SQLException {
        //Get testID from JOB
        //get application_IDs from APPLICATION with applicant_ID and jobID
            //Get name with applicant_ID
            //Get test score with applicantID and testID
            //get status with application_ID
        int testID, applicantID, applicationID, testScore;
        String status, name;
        Object data[] = new Object[6];
        javax.swing.table.DefaultTableModel model = new javax.swing.table.DefaultTableModel() {
            public boolean isCellEditable(int rowIndex, int colIndex) {
                if (colIndex == 0) {
                    return true;
                } else {
                    return false;
                }
            }
            //  Returning the Class of each column will allow different
            //  renderers to be used based on Class
            public Class getColumnClass(int column)
            {
                    return getValueAt(0, column).getClass();
            }
        };
        model.addColumn(new String("Select"));
        model.addColumn(new String("Applicant ID"));
        model.addColumn(new String("Application ID"));
        model.addColumn(new String("Name"));
        model.addColumn(new String("Status"));
        model.addColumn(new String("Test Score"));
        String query = "SELECT TEST_ID " +
                "FROM JOB " +
                "WHERE JOB_ID = " + jobID;
        PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rset = pstmt.executeQuery();
        rset.next();
        testID = rset.getInt(1);
        System.out.println(testID + ":" + query); //DEBUG
        query = "SELECT APPLICANT_ID, APPLICATION_ID FROM APPLICATION " +
                "WHERE JOB_ID = " + jobID;
        pstmt = conn.prepareStatement(query);
        rset = pstmt.executeQuery();
        while(rset.next()) {
            applicantID = rset.getInt(1);
            System.out.println(applicantID + ":" + query); //DEBUG
            applicationID = rset.getInt(2);
            System.out.println(applicationID + ":" + query); //DEBUG
            //NAME
            query = "SELECT A_NAME FROM APPLICANT WHERE APPLICANT_ID = " +
                    applicantID;
            pstmt = conn.prepareStatement(query);
            ResultSet rset2 = pstmt.executeQuery();
            rset2.next();
            name = rset2.getString(1);
            System.out.println(name + ":" + query); //DEBUG
            //TEST SCORE
            query = "SELECT TEST_SCORE FROM TAKES WHERE APPLICANT_ID = " +
                    applicantID + " AND TEST_ID = " + testID;
            pstmt = conn.prepareStatement(query);
            rset2 = pstmt.executeQuery();
            if (rset2.next())
                testScore = rset2.getInt(1);
            else
                testScore = 0;
            System.out.println(testScore + ":" + query); //DEBUG
            //STATUS
            query = "SELECT STATUS FROM APPLICATION_STATUS WHERE APPLICATION_ID = " +
                    applicationID;
            pstmt = conn.prepareStatement(query);
            rset2 = pstmt.executeQuery();
            rset2.next();
            status = rset2.getString(1);
            System.out.println(status + ":" + query); //DEBUG
            
            data[0] = new Boolean(false);
            data[1] = applicantID;
            data[2] = applicationID;
            data[3] = name;
            data[4] = status;
            data[5] = testScore;
            
            model.addRow(data);
        }
        
        return model;
    }
    
    public javax.swing.table.TableModel searchApplicants(String query) 
                                                throws SQLException { 
        if (conn == null)
            throw new IllegalStateException("Connection already closed.");
        searchApplicantID.clear();
        PreparedStatement pstmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                    ResultSet.CONCUR_READ_ONLY);
        ResultSet rs = pstmt.executeQuery();
        javax.swing.table.DefaultTableModel model = new javax.swing.table.DefaultTableModel() {
            public boolean isCellEditable(int rowIndex, int mColIndex) {
                return false;
            }
        };
        
        //Job Title, Employer, Location, Pos. Type, Industry, Min. Salary
        model.addColumn(new String("Name"));
        model.addColumn(new String("Highest Degree"));
        model.addColumn(new String("Birth Year"));
        model.addColumn(new String("Years of Experience"));
        model.addColumn(new String("Citizenship"));
        while (rs.next()) {
            /*For each row, get the fields - must use Object for JTable*/
            Object[] data = new Object[6];
            data[0] = rs.getObject("A_NAME");
            data[1] = rs.getObject("HIGHEST_DEGREE");
            //data[2] = rs.getObject("BIRTH_YEAR");
            data[2] = rs.getObject(6); //Changed so we can format the date better
            data[3] = rs.getObject("YEARS_OF_EXPERIENCE");
            data[4] = rs.getObject("CITIZENSHIP");
            model.addRow(data);
            //Add the APPLICANT_ID to our array list, so we can reference the job if the user wants more detail
            //This will work because the index will be the same as the row clicked on the table
            searchApplicantID.add((int)rs.getInt("APPLICANT_ID"));
        }
        if(model.getRowCount() > 0)
            return model;
        else
            return null;
    }
    
    public boolean applyForJob(int index, boolean testRequired) throws SQLException {
        int retVal = -1;
        int applicationID = -1;
        if ((conn==null) || (searchJobID.isEmpty())) {
            System.out.println("ERROR: Connection to DB is closed or we can't link the " +
                    "table row provided to an actual job_id number.\nTake a look at " +
                    "applyForJob(int) in dbAccess.java.");
            return false;
        }
        int jobID = searchJobID.get(index);
        String query = "INSERT INTO ops$gtg624x.APPLICATION VALUES " +
            "(ApplicationIDSeq.nextval, " + 
            "(select sysdate from dual), " + 
            userID + ", " + jobID + ")";
        PreparedStatement pstmt = conn.prepareStatement(query);
        //insert into OPS$GTG624X.APPLICATION values 
        //  (ApplicationIDSeq.nextval, (select sysdate from dual), userID, jobID)
        retVal = pstmt.executeUpdate();
        if (retVal < 0) {
            return false;
        } else { //get Application ID for App status
            //FIX: Should not have to do this, should be able to combine all 3 queries into 1
            query = "select APPLICATION_ID from ops$gtg624x.APPLICATION " +
                "where APPLICANT_ID = " + userID + " and " +
                "JOB_ID = " + jobID;
        pstmt = conn.prepareStatement(query);
        ResultSet rset = pstmt.executeQuery ();
        if(rset.next()) {
            applicationID = rset.getInt(1); //So we know who we are working for
            }
        }
        //We also need to know if the job requires a test
        String startingStatus;
        if(testRequired) {
            startingStatus = "In test process";
        } else {
            startingStatus = "In interview process";
        }
        if (applicationID > 0) {
            query = "INSERT INTO ops$gtg624x.APPLICATION_STATUS VALUES (" +
                    applicationID + "," + 
                    "'" + startingStatus + "'," +
                    "(select sysdate from dual))";
            pstmt = conn.prepareStatement(query);
            retVal = pstmt.executeUpdate();
        }
        if(retVal < 0) {
            return false;
        } else {
            return true;
        }
    }
    
    public void advanceApplication(int applicationID, String currStatus)
            throws SQLException {
        int i = 0;
        int nextStatus = 3;
        if(currStatus.equals("Accepted") || currStatus.equals("Declined")) {
            //We shouldn't do anything.
        } else {
            for(String aStatus : statusStrings) {
                if(aStatus.equals(currStatus)) {
                    nextStatus = i + 1;
                    break;
                }
                i++;
            }
            String query = "INSERT INTO APPLICATION_STATUS VALUES(" +
                    applicationID + "," +
                    "'" + statusStrings[nextStatus] + "'," + 
                    "(select sysdate from dual))";
            PreparedStatement pstmt = conn.prepareStatement(query);
            pstmt.executeUpdate();
        }
    }
    
    public void declineApplication(int applicationID) throws SQLException {
        String query = "INSERT INTO APPLICATION_STATUS VALUES(" +
                    applicationID + "," +
                    "'" + statusStrings[4] + "'," + 
                    "(select sysdate from dual))";
        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.executeUpdate();
    }
    
    public void applicantTakesTest(int applicantID, int jobID, int score) 
            throws SQLException {
        int testID = 0;
        String query = "SELECT TEST_ID FROM JOB WHERE JOB_ID = " + jobID;
        PreparedStatement pstmt = conn.prepareStatement(query);
        ResultSet rset = pstmt.executeQuery();
        rset.next();
        testID = rset.getInt(1);
        
        query = "INSERT INTO TAKES VALUES(" + 
                applicantID + "," +
                testID + "," +
                score + "," +
                "(select sysdate from dual))";
        pstmt = conn.prepareStatement(query);
        pstmt.executeUpdate();
    }
    
    public void closeJob(int jobID) throws SQLException {
        //Set date_job_filled
        //Get all application_id values that correspond to jobID
        //run declineApplication for each of those application_id's
        String query = "UPDATE JOB SET " + 
                "DATE_JOB_FILLED = (select sysdate from dual) " + 
                "WHERE JOB_ID = " + jobID;
        PreparedStatement pstmt = conn.prepareStatement(query);
        pstmt.executeUpdate();
        
        query = "SELECT APPLICATION_ID FROM APPLICATION WHERE " + 
                "JOB_ID = " + jobID + " AND " + 
                "";
        pstmt = conn.prepareStatement(query);
        ResultSet rset = pstmt.executeQuery();
        while(rset.next()) {
            int applicationID = rset.getInt(1);
            boolean declineIt = true;
            query = "SELECT STATUS FROM APPLICATION_STATUS WHERE " +
                    "APPLICATION_ID = " + applicationID;
            pstmt = conn.prepareStatement(query);
            ResultSet rset2 = pstmt.executeQuery();
            if(rset2.last()) {
                String status = rset2.getString(1);
                if(status.equals("Accepted") || status.equals("Declined")) {
                    //Leave it alone
                    declineIt = false;
                }
            }
            if(declineIt)
                declineApplication(applicationID);
        }
    }
    
    public void close() {
        try { conn.close(); } // Try to close the connection
	catch (Exception e) {}      // Do nothing on error. At least we tried.
	conn = null; 
    }
    
    /** Automatically close the connection when we're garbage collected */
    protected void finalize() { close(); }
}
